/*
 * Author: Minh-Tuan Ta
 * Date: 11/6/11
 * Class: ResourceDB
 * This class used to access the data from Resource table
 */
package data;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.*;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import beans.*;

public class ResourceDB extends DB {
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/7/11
	 * Method: getResources
	 * This method will get all the resources in the database
	 */
	public static synchronized ArrayList<Resource> getResources()
	{
		Connection conn = null;
		ArrayList<Resource> resources = new ArrayList<Resource>();
		PreparedStatement statement = null;
		String sql = "SELECT resourceid, resourcename, path, contentid\n" +
					 "FROM resource";
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			
			while (rs.next())
			{
				Resource resource = new Resource();
				setResource(resource, rs);
				
				resources.add(resource);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return resources;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/7/11
	 * Method: getResource
	 * This method will get all the resource of a content using content id
	 */
	public static synchronized Resource getResourceByContentID(int contentid)
	{
		Connection conn = null;
		Resource resource = null;
		PreparedStatement statement = null;
		String sql = "SELECT resourceid, resourcename, path, contentid\n" +
					 "FROM resource\n" +
					 "WHERE contentid = ?";
		
		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, contentid);
			ResultSet rs = statement.executeQuery();
			
			if (rs.next())
			{
				resource = new Resource();
				setResource(resource, rs);
			}
			
			rs.close();
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return resource;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/7/11
	 * Method: addResource
	 * This method will add a new Resource to the database
	 */
	public static synchronized int addResource(Resource resource)
	{
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "INSERT INTO resource(resourcename, path, contentid)\n" +
					 "VALUES(?, ?, ?)";

		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setString(1, resource.getResourceName());
			statement.setString(2, resource.getPath());
			statement.setInt(3, resource.getContentID());
			
			status = statement.executeUpdate();
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return status;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/7/11
	 * Method: addResource
	 * This method will update a Resource in the database
	 */
	public static synchronized int updateResource(Resource resource)
	{
		Connection conn = null;
		int status = 0;
		PreparedStatement statement = null;
		String sql = "UPDATE resource\n" +
					 "SET resourcename = ?, path = ?, contentid = ?\n" +
					 "WHERE resourceid = ?";

		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setString(1, resource.getResourceName());
			statement.setString(2, resource.getPath());
			statement.setInt(3, resource.getContentID());
			statement.setInt(4, resource.getResourceID());
			
			status = statement.executeUpdate();
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return status;
	}
	
	/*
	 * Author: Minh-Tuan Ta
	 * Date: 11/7/11
	 * Method: deleteResource
	 * This method will delete a Resource in the database
	 */
	public static synchronized int deleteResource(Resource resource, HttpServletRequest request)
	{
		Connection conn = null;
		int status = 0;
		
		//delete the file first
		resource = getResourceByContentID(resource.getContentID());
		String realPath = request.getSession().getServletContext().getRealPath("/" + resource.getPath());
		
		File file = new File(realPath);
		
		file.delete();
		
		PreparedStatement statement = null;
		String sql = "DELETE FROM resource\n" +
					 "WHERE resourceid = ?";

		try
		{
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, resource.getResourceID());
			
			status = statement.executeUpdate();
			
			
			statement.close();
			conn.close();
		}
		catch (SQLException ex)
		{
			System.out.println("Error: " + ex);
			System.out.println("Query: " + statement.toString());
		}
		
		return status;

	}
}
